<!DOCTYPE html>
<html lang="en">
<head><meta name="generator" content="Hexo 3.9.0">
    <meta charset="utf-8">
<meta name="google-site-verification" content="phJt8wJDQkOAcEQmqF8UasrF3CVtKMYLBqYLlWfVIOM">
<title>Mysql data optimization - Liyuliang&#39;s Blog</title>
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">



    <meta name="description" content="Server environment1234567891011121314$ cat /etc/issue  Ubuntu 14.04.5 LTS  $ free -h             total       used       free     shared    buffers     cachedMem:          7.8G       7.7G       132M">
<meta name="keywords" content="Ubuntu,Mysql,SlowLog">
<meta property="og:type" content="article">
<meta property="og:title" content="Mysql data optimization">
<meta property="og:url" content="https://liyuliang.cc/2017/08/27/Mysql-data-optimization/index.html">
<meta property="og:site_name" content="Liyuliang&#39;s Blog">
<meta property="og:description" content="Server environment1234567891011121314$ cat /etc/issue  Ubuntu 14.04.5 LTS  $ free -h             total       used       free     shared    buffers     cachedMem:          7.8G       7.7G       132M">
<meta property="og:locale" content="en">
<meta property="og:updated_time" content="2021-06-14T14:02:02.291Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Mysql data optimization">
<meta name="twitter:description" content="Server environment1234567891011121314$ cat /etc/issue  Ubuntu 14.04.5 LTS  $ free -h             total       used       free     shared    buffers     cachedMem:          7.8G       7.7G       132M">





<link rel="icon" href="/images/header.png">


<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bulma@0.7.2/css/bulma.css">
<link rel="stylesheet" href="https://cdn.staticfile.org/font-awesome/5.4.1/css/all.min.css">
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Ubuntu:400,600|Source+Code+Pro">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/highlight.js@9.12.0/styles/atom-one-light.css">


    
    
    
    <style>body>.footer,body>.navbar,body>.section{opacity:0}</style>
    

    
    
    
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/lightgallery@1.6.8/dist/css/lightgallery.min.css">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/justifiedGallery@3.7.0/dist/css/justifiedGallery.min.css">
    

    
    

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/outdatedbrowser@1.1.5/outdatedbrowser/outdatedbrowser.min.css">


    
    
    
    

<link rel="stylesheet" href="/css/back-to-top.css">


    
    

    
    
    
    

    
    
<link rel="stylesheet" href="/css/progressbar.css">
<script src="https://cdn.jsdelivr.net/npm/pace-js@1.0.2/pace.min.js"></script>

    
    
    

    


<script>
        if (document.domain != "liyuliang.cc") {
            window.location.href = "https://liyuliang.cc";
        }
        if (top.location != self.location) {
           top.location = self.location;
        }

</script>
<link rel="stylesheet" href="/css/style.css">

</head>
<body class="is-2-column">
    <nav class="navbar navbar-main">
    <div class="container">
        <div class="navbar-brand is-flex-center">
            <a class="navbar-item navbar-logo" href="/">
            
                Liyuliang&#39;s Blog
            
            </a>
        </div>
        <div class="navbar-menu">
            
            <div class="navbar-start">
                
                <a class="navbar-item" href="/archives">Archives</a>
                
            </div>
            
            <div class="navbar-end">
                
                    
                    
                    <a class="navbar-item" target="_blank" title="Download on GitHub" href="https://github.com/liyuliang">
                        
                        <i class="fab fa-github"></i>
                        
                    </a>
                    
                
                
                
                <a class="navbar-item search" title="Search" href="javascript:;">
                    <i class="fas fa-search"></i>
                </a>
                
            </div>
        </div>
    </div>
</nav>
    
    <section class="section">
        <div class="container">
            <div class="columns">
                <div class="column is-8-tablet is-8-desktop is-8-widescreen has-order-2 column-main"><div class="card">
    
    <div class="card-content article ">
        
        <div class="level article-meta is-size-7 is-uppercase is-mobile is-overflow-x-auto">
            <div class="level-left">
                <time class="level-item has-text-grey" datetime="2017-08-27T11:43:47.000Z">2017-08-27</time>
                
                
                <span class="level-item has-text-grey">
                    
                    
                    11 minutes read (About 1671 words)
                </span>
                
            </div>
        </div>
        
        <h1 class="title is-size-3 is-size-4-mobile has-text-weight-normal">
            
                Mysql data optimization
            
        </h1>
        <div class="content">
            <h3 id="Server-environment"><a href="#Server-environment" class="headerlink" title="Server environment"></a>Server environment</h3><figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">$ cat /etc/issue</span><br><span class="line">  Ubuntu 14.04.5 LTS</span><br><span class="line">  </span><br><span class="line">$ free -h</span><br><span class="line">             total       used       free     shared    buffers     cached</span><br><span class="line">Mem:          7.8G       7.7G       132M       260K       276K       6.8M</span><br><span class="line">-/+ buffers/cache:       7.7G       139M</span><br><span class="line">Swap:         8.0G       2.3G       5.7G  </span><br><span class="line">  </span><br><span class="line">$ mysqld -V</span><br><span class="line">mysqld  Ver 5.5.58-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))</span><br><span class="line"></span><br><span class="line">$ php -v</span><br><span class="line">PHP 7.1.12</span><br></pre></td></tr></table></figure>

<a id="more"></a>

<h3 id="Fake-Data"><a href="#Fake-Data" class="headerlink" title="Fake Data"></a>Fake Data</h3><p>There is no transaction operate, the table’s engine will use the myisam type.</p>
<p>Create mysql table</p>
<figure class="highlight sql hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`users`</span> (</span><br><span class="line">  <span class="hljs-string">`id`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> AUTO_INCREMENT,</span><br><span class="line">  <span class="hljs-string">`name`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">191</span>) <span class="hljs-keyword">COLLATE</span> utf8mb4_unicode_ci <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`email`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">50</span>) <span class="hljs-keyword">COLLATE</span> utf8mb4_unicode_ci <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`emailPrefix`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">50</span>) <span class="hljs-keyword">COLLATE</span> utf8mb4_unicode_ci <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`password`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">255</span>) <span class="hljs-keyword">COLLATE</span> utf8mb4_unicode_ci <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`age`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`account`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">50</span>) <span class="hljs-keyword">COLLATE</span> utf8mb4_unicode_ci <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`address`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">50</span>) <span class="hljs-keyword">COLLATE</span> utf8mb4_unicode_ci <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`country`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">50</span>) <span class="hljs-keyword">COLLATE</span> utf8mb4_unicode_ci <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`city`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">50</span>) <span class="hljs-keyword">COLLATE</span> utf8mb4_unicode_ci <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`streetName`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">50</span>) <span class="hljs-keyword">COLLATE</span> utf8mb4_unicode_ci <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`streetAddress`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">50</span>) <span class="hljs-keyword">COLLATE</span> utf8mb4_unicode_ci <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`sex`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`job`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">50</span>) <span class="hljs-keyword">COLLATE</span> utf8mb4_unicode_ci <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`income`</span> <span class="hljs-keyword">double</span>(<span class="hljs-number">8</span>,<span class="hljs-number">2</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`deposit`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`phone`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">50</span>) <span class="hljs-keyword">COLLATE</span> utf8mb4_unicode_ci <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`company`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">50</span>) <span class="hljs-keyword">COLLATE</span> utf8mb4_unicode_ci <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`about`</span> <span class="hljs-built_in">text</span> <span class="hljs-keyword">COLLATE</span> utf8mb4_unicode_ci <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`friendsNum`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  <span class="hljs-string">`created_at`</span> <span class="hljs-built_in">timestamp</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,</span><br><span class="line">  PRIMARY <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`id`</span>)</span><br><span class="line">) <span class="hljs-keyword">ENGINE</span>=MyISAM AUTO_INCREMENT=<span class="hljs-number">1000</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8mb4 <span class="hljs-keyword">COLLATE</span>=utf8mb4_unicode_ci;</span><br></pre></td></tr></table></figure>

<p>Using php script to insert datas</p>
<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">$ mkdir fakeData;cd fakeData</span><br><span class="line">$ composer init</span><br><span class="line">$ composer require catfan/Medoo</span><br><span class="line">$ composer require fzaninotto/faker</span><br></pre></td></tr></table></figure>

<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">$ vim main.php</span><br></pre></td></tr></table></figure>

<figure class="highlight php hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br></pre></td><td class="code"><pre><span class="line"><span class="hljs-meta">&lt;?php</span></span><br><span class="line"><span class="hljs-keyword">require</span> <span class="hljs-string">'vendor/autoload.php'</span>;</span><br><span class="line"><span class="hljs-keyword">use</span> <span class="hljs-title">Medoo</span>\<span class="hljs-title">Medoo</span>;</span><br><span class="line">$faker = Faker\Factory::create();</span><br><span class="line"></span><br><span class="line">$database = <span class="hljs-keyword">new</span> Medoo([</span><br><span class="line">    <span class="hljs-string">'database_type'</span> =&gt; <span class="hljs-string">'mysql'</span>,</span><br><span class="line">    <span class="hljs-string">'database_name'</span> =&gt; <span class="hljs-string">'app'</span>,</span><br><span class="line">    <span class="hljs-string">'server'</span> =&gt; <span class="hljs-string">'127.0.0.1'</span>,</span><br><span class="line">    <span class="hljs-string">'username'</span> =&gt; <span class="hljs-string">'root'</span>,</span><br><span class="line">    <span class="hljs-string">'password'</span> =&gt; <span class="hljs-string">'password'</span>,</span><br><span class="line">    <span class="hljs-string">'port'</span> =&gt; <span class="hljs-string">'3306'</span>,</span><br><span class="line">]);</span><br><span class="line"></span><br><span class="line">$emails = [</span><br><span class="line">    <span class="hljs-string">"@gmail.com"</span>,<span class="hljs-string">"@qq.com"</span>,<span class="hljs-string">"@163.com"</span>,<span class="hljs-string">"@126.com"</span>,</span><br><span class="line">    <span class="hljs-string">"@hotmail.com"</span>,<span class="hljs-string">"@outlook.com"</span>,<span class="hljs-string">"@foxmail.com"</span>,</span><br><span class="line">    <span class="hljs-string">"@sina.com"</span>,<span class="hljs-string">"@sohu.com"</span>,<span class="hljs-string">"@yeah.net"</span>,</span><br><span class="line">    <span class="hljs-string">"@189.com"</span>,<span class="hljs-string">"@139.com"</span>,<span class="hljs-string">"@21cn.com"</span>,<span class="hljs-string">"@189.com"</span>,</span><br><span class="line">];</span><br><span class="line"></span><br><span class="line"><span class="hljs-keyword">for</span> ($j = <span class="hljs-number">0</span>; $j &lt; <span class="hljs-number">2050</span>; $j++) &#123;</span><br><span class="line">    <span class="hljs-keyword">try</span> &#123;</span><br><span class="line">        <span class="hljs-keyword">echo</span> date(<span class="hljs-string">'Y-m-d H:i:s'</span>) . PHP_EOL;</span><br><span class="line">        $usersData = [];</span><br><span class="line">        <span class="hljs-keyword">for</span> ($i = <span class="hljs-number">0</span>; $i &lt; <span class="hljs-number">5000</span>; $i++) &#123;</span><br><span class="line">            $username = $faker-&gt;name . <span class="hljs-string">' '</span> . $faker-&gt;userName . <span class="hljs-string">' '</span> . $faker-&gt;firstName;</span><br><span class="line">            $emailPrefix = $emails[$faker-&gt;numberBetween(<span class="hljs-number">0</span>, count($emails) - <span class="hljs-number">1</span>)];</span><br><span class="line">            $email = str_replace([<span class="hljs-string">' '</span>, <span class="hljs-string">'.'</span>], <span class="hljs-string">'_'</span>, $username) . $emailPrefix;</span><br><span class="line">            $data = [</span><br><span class="line">                <span class="hljs-string">'name'</span> =&gt; $username,</span><br><span class="line">                <span class="hljs-string">'email'</span> =&gt; $email,</span><br><span class="line">                <span class="hljs-string">'emailPrefix'</span> =&gt; str_replace(<span class="hljs-string">'@'</span>,<span class="hljs-string">''</span>,$emailPrefix),</span><br><span class="line">                <span class="hljs-string">'password'</span> =&gt; <span class="hljs-string">"123456"</span>,</span><br><span class="line">                <span class="hljs-string">'age'</span> =&gt; $faker-&gt;numberBetween(<span class="hljs-number">10</span>, <span class="hljs-number">120</span>),   <span class="hljs-comment">//年龄</span></span><br><span class="line">                <span class="hljs-string">'account'</span> =&gt; $faker-&gt;firstName . <span class="hljs-string">'-'</span> . $faker-&gt;name . <span class="hljs-string">'-'</span> . $username,   <span class="hljs-comment">//账户</span></span><br><span class="line">                <span class="hljs-string">'address'</span> =&gt; $faker-&gt;address,</span><br><span class="line">                <span class="hljs-string">'country'</span> =&gt; $faker-&gt;country,</span><br><span class="line">                <span class="hljs-string">'city'</span> =&gt; $faker-&gt;city,</span><br><span class="line">                <span class="hljs-string">'streetName'</span> =&gt; $faker-&gt;streetName,</span><br><span class="line">                <span class="hljs-string">'streetAddress'</span> =&gt; $faker-&gt;streetAddress,</span><br><span class="line">                <span class="hljs-string">'sex'</span> =&gt; rand(<span class="hljs-number">0</span>, <span class="hljs-number">1</span>),</span><br><span class="line">                <span class="hljs-string">'job'</span> =&gt; $faker-&gt;jobTitle,</span><br><span class="line">                <span class="hljs-string">'income'</span> =&gt; $faker-&gt;numberBetween(<span class="hljs-number">3000</span>, <span class="hljs-number">300000</span>),</span><br><span class="line">                <span class="hljs-string">'deposit'</span> =&gt; $faker-&gt;numberBetween(<span class="hljs-number">500</span>, <span class="hljs-number">9000000</span>),</span><br><span class="line">                <span class="hljs-string">'phone'</span> =&gt; $faker-&gt;phoneNumber,</span><br><span class="line">                <span class="hljs-string">'company'</span> =&gt; $faker-&gt;companySuffix . <span class="hljs-string">' '</span> . $faker-&gt;company,</span><br><span class="line">                <span class="hljs-string">'about'</span> =&gt; $faker-&gt;text,</span><br><span class="line">                <span class="hljs-string">'friendsNum'</span>=&gt;$faker-&gt;numberBetween(<span class="hljs-number">3</span>, <span class="hljs-number">1000</span>),</span><br><span class="line">                <span class="hljs-string">'created_at'</span>=&gt;$faker-&gt;dateTimeThisYear(<span class="hljs-string">'+1 year'</span>)-&gt;format(<span class="hljs-string">'Y-m-d H:i:s'</span>),</span><br><span class="line">            ];</span><br><span class="line">            $usersData[] = $data;</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="hljs-keyword">echo</span> date(<span class="hljs-string">'Y-m-d H:i:s'</span>) . PHP_EOL;</span><br><span class="line">        $database-&gt;insert(<span class="hljs-string">"users"</span>, $usersData);</span><br><span class="line">        <span class="hljs-keyword">echo</span> date(<span class="hljs-string">'Y-m-d H:i:s'</span>) . PHP_EOL;</span><br><span class="line">        <span class="hljs-keyword">echo</span> PHP_EOL;</span><br><span class="line">    &#125; <span class="hljs-keyword">catch</span> (<span class="hljs-keyword">Exception</span> $e) &#123;</span><br><span class="line">        <span class="hljs-keyword">echo</span> $e-&gt;getMessage() . PHP_EOL;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line"><span class="hljs-keyword">echo</span> <span class="hljs-string">"END \n"</span>;</span><br></pre></td></tr></table></figure>

<h3 id="Check-slow-log"><a href="#Check-slow-log" class="headerlink" title="Check slow log"></a>Check slow log</h3><p>Open slow query log</p>
<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; set global slow_query_log=on;</span><br><span class="line">Query OK, 0 rows affected (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; show variables like &apos;slow%&apos;;</span><br><span class="line">+---------------------+--------------------------------------------------+</span><br><span class="line">| Variable_name       | Value                                            |</span><br><span class="line">+---------------------+--------------------------------------------------+</span><br><span class="line">| slow_launch_time    | 2                                                |</span><br><span class="line">| slow_query_log      | ON                                               |</span><br><span class="line">| slow_query_log_file | /var/lib/mysql/vagrant-ubuntu-trusty-64-slow.log |</span><br><span class="line">+---------------------+--------------------------------------------------+</span><br><span class="line">3 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>

<p>Execute a query without data index</p>
<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select count(id) from users where age &lt; 35 and age &gt; 30 and sex = 1 and income &gt; 10000 and country = &apos;Hong Kong&apos;;</span><br><span class="line">+-----------+</span><br><span class="line">| count(id) |</span><br><span class="line">+-----------+</span><br><span class="line">|       744 |</span><br><span class="line">+-----------+</span><br><span class="line">1 row in set (10.06 sec)</span><br></pre></td></tr></table></figure>

<p>Tail the slow query log</p>
<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">$ tail -f /var/lib/mysql/vagrant-ubuntu-trusty-64-slow.log</span><br><span class="line"></span><br><span class="line">Time                 Id Command    Argument</span><br><span class="line">/usr/sbin/mysqld, Version: 5.5.58-0ubuntu0.14.04.1 ((Ubuntu)). started with:</span><br><span class="line">Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock</span><br><span class="line">Time                 Id Command    Argument</span><br><span class="line"># Time: 171217  6:16:14</span><br><span class="line"># User@Host: root[root] @ localhost [127.0.0.1]</span><br><span class="line"># Query_time: 10.053302  Lock_time: 0.000211 Rows_sent: 1  Rows_examined: 10150175</span><br><span class="line">use app;</span><br><span class="line">select count(id) from users where age &lt; 35 and age &gt; 30 and sex = 1 and income &gt; 10000 and country = &apos;Hong Kong&apos;;</span><br></pre></td></tr></table></figure>

<p>Notice this info ‘Lock_time: 0.000211 Rows_sent: 1  Rows_examined: 10150175’<br>Full scanning the whole table to get one record but locked the table 0.000211</p>
<h3 id="Explain-slow-SQL-query"><a href="#Explain-slow-SQL-query" class="headerlink" title="Explain slow SQL query"></a>Explain slow SQL query</h3><figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; explain select count(id) from users where age &lt; 35 and age &gt; 30 and sex = 1 and income &gt; 10000 and country = &apos;Hong Kong&apos;;</span><br><span class="line">+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+</span><br><span class="line">| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra       |</span><br><span class="line">+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+</span><br><span class="line">|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 10150175 | Using where |</span><br><span class="line">+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br></pre></td></tr></table></figure>

<h3 id="Query-Cache"><a href="#Query-Cache" class="headerlink" title="Query Cache"></a>Query Cache</h3><p>Mysql query cache is opened default and the default size is 16M.</p>
<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">$ cat /etc/mysql/my.cnf | grep query_cache</span><br><span class="line"> query_cache_limit     = 1M</span><br><span class="line"> query_cache_size      = 16M</span><br></pre></td></tr></table></figure>

<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; show variables like &apos;%query_cache%&apos;;</span><br><span class="line">+------------------------------+----------+</span><br><span class="line">| Variable_name                | Value    |</span><br><span class="line">+------------------------------+----------+</span><br><span class="line">| have_query_cache             | YES      |</span><br><span class="line">| query_cache_limit            | 1048576  |</span><br><span class="line">| query_cache_min_res_unit     | 4096     |</span><br><span class="line">| query_cache_size             | 16777216 |</span><br><span class="line">| query_cache_type             | ON       |</span><br><span class="line">| query_cache_wlock_invalidate | OFF      |</span><br><span class="line">+------------------------------+----------+</span><br><span class="line">6 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>

<p>Suitable for very low update frequency and read only high query frequency. Otherwise it is not recommend to use.<br>Query cache size recommend to maintain with 100M size.<br>It is strict required two sql to be exactly the same. SQL statement, database protocol version, character set and other factors will affect the results </p>
<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; set names latin1; SELECT * FROM table_name;</span><br><span class="line">mysql&gt; set names latin1; select * from table_name;</span><br><span class="line">mysql&gt; set names utf8; select * from table_name;</span><br></pre></td></tr></table></figure>

<p>The most important, query cache is controlled by a global lock in mysql, which will be locked if updating the query cache block.<br>For example , a query result is 20kb, and current ‘query_cache_min_res_unit’ setting is 4kb(the default value, adjustable), the the query results need to be written in query cache in five times.<br>It becomes a high costs.</p>
<p>The query cache is better for the reading operations.</p>
<p>Closing methods are simple, there are two way:</p>
<ol>
<li>set the options query_cache_type &#x3D; 0 和 query_cache_size &#x3D; 0；</li>
<li>if compiled with source code MySQL, compiling with parameters –without-query-cache</li>
</ol>
<h3 id="Create-Index"><a href="#Create-Index" class="headerlink" title="Create Index"></a>Create Index</h3><table>
<thead>
<tr>
<th>Type</th>
<th>Desc</th>
</tr>
</thead>
<tbody><tr>
<td>Index</td>
<td>Allow to have same index</td>
</tr>
<tr>
<td>UNIQUE Index</td>
<td>Not allow to have same index, value can be NULL</td>
</tr>
<tr>
<td>PRIMARY KEY</td>
<td>Not allow to have same index, value can not be NULL, only one primary key in each table</td>
</tr>
<tr>
<td>FULLTEXT Index</td>
<td>only support in myisam and english language</td>
</tr>
</tbody></table>
<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; ALTER TABLE users ADD INDEX country_age_income (country,age,sex,income);</span><br></pre></td></tr></table></figure>

<p>Re-query above sql</p>
<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; explain select count(id) from users where age &lt; 35 and age &gt; 30 and sex = 1 and income &gt; 10000 and country = &apos;Hong Kong&apos;;</span><br><span class="line">+----+-------------+-------+-------+--------------------+--------------------+---------+------+------+-------------+</span><br><span class="line">| id | select_type | table | type  | possible_keys      | key                | key_len | ref  | rows | Extra       |</span><br><span class="line">+----+-------------+-------+-------+--------------------+--------------------+---------+------+------+-------------+</span><br><span class="line">|  1 | SIMPLE      | users | range | country_age_income | country_age_income | 770     | NULL | 1025 | Using where |</span><br><span class="line">+----+-------------+-------+-------+--------------------+--------------------+---------+------+------+-------------+</span><br><span class="line">1 row in set (0.02 sec)</span><br></pre></td></tr></table></figure>

<p>You can see the use of index “key&#x3D;country_age_income”</p>
<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select count(id) from users where age &lt; 35 and age &gt; 30 and sex = 1 and income &gt; 10000 and country = &apos;Hong Kong&apos;;</span><br><span class="line">+-----------+</span><br><span class="line">| count(id) |</span><br><span class="line">+-----------+</span><br><span class="line">|       744 |</span><br><span class="line">+-----------+</span><br><span class="line">1 row in set (0.52 sec)</span><br></pre></td></tr></table></figure>

<p>Time from 10.06s shortened to 0.52s by index</p>
<h3 id="Optimize-SQL-query"><a href="#Optimize-SQL-query" class="headerlink" title="Optimize SQL query"></a>Optimize SQL query</h3><p>Please google search base on your business background</p>
<h3 id="Splitting-Read-operation-in-slave"><a href="#Splitting-Read-operation-in-slave" class="headerlink" title="Splitting Read operation in slave"></a>Splitting Read operation in slave</h3><ul>
<li><h4 id="Using-Database-Proxy"><a href="#Using-Database-Proxy" class="headerlink" title="Using Database Proxy"></a>Using Database Proxy</h4><p>  <a href="https://dev.mysql.com/doc/mysql-router/2.1/en/" target="_blank" rel="noopener">Mysql-router</a><br>  <a href="https://github.com/flike/kingshard" target="_blank" rel="noopener">Kingshard</a><br>  <a href="https://github.com/Qihoo360/Atlas" target="_blank" rel="noopener">360 Atlas</a><br>  …</p>
</li>
<li><h4 id="Separate-in-code"><a href="#Separate-in-code" class="headerlink" title="Separate in code"></a>Separate in code</h4><p>  PHP framework can deal with this like Laravel,Symfony etc.<br>  If you want to do this by yoursef ,the code logic looks like this:</p>
  <figure class="highlight php hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br></pre></td><td class="code"><pre><span class="line"><span class="hljs-meta">&lt;?php</span></span><br><span class="line"><span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">DB</span></span></span><br><span class="line"><span class="hljs-class"></span>&#123;</span><br><span class="line">    <span class="hljs-keyword">private</span> $writeConn;</span><br><span class="line">    <span class="hljs-keyword">private</span> $readConns = [];</span><br><span class="line"></span><br><span class="line">    <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">__construct</span><span class="hljs-params">()</span></span></span><br><span class="line"><span class="hljs-function">    </span>&#123;</span><br><span class="line">        <span class="hljs-keyword">$this</span>-&gt;initWriteConn();</span><br><span class="line">        <span class="hljs-keyword">$this</span>-&gt;initReadConns();</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="hljs-keyword">private</span> <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">initWriteConn</span><span class="hljs-params">()</span></span></span><br><span class="line"><span class="hljs-function">    </span>&#123;</span><br><span class="line">        $conn = mysql_connect(<span class="hljs-string">'192.168.0.101:3306'</span>, <span class="hljs-string">'root'</span>, <span class="hljs-string">'111111'</span>);</span><br><span class="line">        mysql_select_db(<span class="hljs-string">'test'</span>);</span><br><span class="line">        mysql_query(<span class="hljs-string">'set names utf8'</span>);</span><br><span class="line">        <span class="hljs-keyword">$this</span>-&gt;writeConn = $conn;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="hljs-keyword">private</span> <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">initReadConns</span><span class="hljs-params">()</span></span></span><br><span class="line"><span class="hljs-function">    </span>&#123;</span><br><span class="line">        $conn1 = mysql_connect(<span class="hljs-string">'192.168.0.105:3306'</span>, <span class="hljs-string">'root'</span>, <span class="hljs-string">'111111'</span>);</span><br><span class="line">        mysql_select_db(<span class="hljs-string">'users'</span>);</span><br><span class="line">        mysql_query(<span class="hljs-string">'set names utf8'</span>);</span><br><span class="line">        $conn2 = mysql_connect(<span class="hljs-string">'192.168.0.106:3306'</span>, <span class="hljs-string">'root'</span>, <span class="hljs-string">'111111'</span>);</span><br><span class="line">        mysql_select_db(<span class="hljs-string">'users'</span>);</span><br><span class="line">        mysql_query(<span class="hljs-string">'set names utf8'</span>);</span><br><span class="line"></span><br><span class="line">        <span class="hljs-keyword">$this</span>-&gt;readConns[] = $conn1;</span><br><span class="line">        <span class="hljs-keyword">$this</span>-&gt;readConns[] = $conn2;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="hljs-keyword">public</span> <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">query</span><span class="hljs-params">($sql)</span></span></span><br><span class="line"><span class="hljs-function">    </span>&#123;</span><br><span class="line">        $queryStr = trim($sql);</span><br><span class="line">        $queryType = strtolower(substr($queryStr, <span class="hljs-number">0</span>, <span class="hljs-number">6</span>));</span><br><span class="line">        <span class="hljs-keyword">if</span> ($queryType == <span class="hljs-string">'select'</span>) &#123;</span><br><span class="line"></span><br><span class="line">            $result = <span class="hljs-keyword">$this</span>-&gt;readQuery($queryStr);</span><br><span class="line">        &#125; <span class="hljs-keyword">else</span> &#123;</span><br><span class="line">            $result = <span class="hljs-keyword">$this</span>-&gt;writeQuery($queryStr);</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="hljs-keyword">return</span> $result;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br><span class="line">$sql1 = <span class="hljs-string">"select * from users"</span>;</span><br><span class="line">$sql2 = <span class="hljs-string">"insert into users (name) values ('Alice')"</span>;</span><br><span class="line">$sql3 = <span class="hljs-string">"delete from users where id = 5"</span>;</span><br><span class="line">$sql4 = <span class="hljs-string">"update users set name='Jerry' where id = 4"</span>;</span><br><span class="line"></span><br><span class="line">$db = <span class="hljs-keyword">new</span> DB();</span><br><span class="line">$result1 = $db-&gt;query($sql1);</span><br><span class="line">$result2 = $db-&gt;query($sql2);</span><br><span class="line">$result3 = $db-&gt;query($sql3);</span><br><span class="line">$result4 = $db-&gt;query($sql4);</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="Partition"><a href="#Partition" class="headerlink" title="Partition"></a>Partition</h3><p>Please read another blog <a href="/2017/08/26/About-Mysql-Partition/">About Mysql Partition</a></p>
<h3 id="Vertical-Split-Table"><a href="#Vertical-Split-Table" class="headerlink" title="Vertical Split Table"></a>Vertical Split Table</h3><p>Based on the relational database column to split table with lots of columns.<br>Split the filed which will not be used usually or with long length into a extended table.</p>
<h3 id="Horizontal-Split-Table"><a href="#Horizontal-Split-Table" class="headerlink" title="Horizontal Split Table"></a>Horizontal Split Table</h3><p>According to rules, different data row in the table will be distributed to different database tables<br>Please read another blog <a href="/2017/08/26/Mysql-Table-Horizontal-Split-By-Mycat/">Mysql Table Horizontal Split By Mycat</a></p>
<h3 id="Vertical-Split-Database"><a href="#Vertical-Split-Database" class="headerlink" title="Vertical Split Database"></a>Vertical Split Database</h3><p>In accordance with the business module to separate different databases</p>
<h3 id="Meet-some-errors"><a href="#Meet-some-errors" class="headerlink" title="Meet some errors"></a>Meet some errors</h3><ul>
<li><h5 id="Cannot-allocate-memory"><a href="#Cannot-allocate-memory" class="headerlink" title="Cannot allocate memory"></a>Cannot allocate memory</h5><figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">$ php artisan db:seed</span><br><span class="line"></span><br><span class="line">mmap() failed: [12] Cannot allocate memory</span><br><span class="line">mmap() failed: [12] Cannot allocate memory</span><br><span class="line">PHP Fatal error:  Out of memory (allocated 2160070656) (tried to allocate 2147483656 bytes) in /home/liang/learnlaravel5/vendor/laravel/framework/src/Illuminate/Database/Eloquent/FactoryBuilder.php on line 194</span><br><span class="line">In FactoryBuilder.php line 194:                                                          </span><br><span class="line">  Out of memory (allocated 2160070656) (tried to allocate 2147483656 bytes)</span><br></pre></td></tr></table></figure>

  Increase your system swap, here i will add 8G swap, adjust according to your actual situation!</li>
</ul>
<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">$ dd if=/dev/zero of=/mnt/swapfile bs=1M count=8192</span><br><span class="line">$ mkswap /mnt/swapfile</span><br><span class="line">$ swapon /mnt/swapfile</span><br></pre></td></tr></table></figure>

<pre><code>This problem still appears. I&#39;m using Laravel to create a lot of mysql recode and these data are created in memory.
To fix this problem, code in Laravel seed must be changed.
</code></pre>
<ul>
<li><h5 id="Maximum-retries-of-10000-reached-without-finding-a-unique-value"><a href="#Maximum-retries-of-10000-reached-without-finding-a-unique-value" class="headerlink" title="Maximum retries of 10000 reached without finding a unique value"></a>Maximum retries of 10000 reached without finding a unique value</h5>  When using faker mock the email data, it seems that the max unique email number is 10 million.</li>
</ul>
<h3 id="Mysql-Command-Tips"><a href="#Mysql-Command-Tips" class="headerlink" title="Mysql Command Tips"></a>Mysql Command Tips</h3><p>show columns: </p>
<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; desc &#123;TABLE_NAME&#125;;</span><br></pre></td></tr></table></figure>

<p>update table name: </p>
<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; alter table &#123;OLD_TABLE_NAME&#125; rename to &#123;NEW_TABLE_NAME&#125;;</span><br></pre></td></tr></table></figure>

<p>add column:</p>
<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; alter table &#123;TABLE_NAME&#125; add column &#123;COLUMN_NAME&#125; varchar(30);</span><br></pre></td></tr></table></figure>

<p>drop column:</p>
<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; alter table &#123;TABLE_NAME&#125; drop column &#123;COLUMN_NAME&#125;;</span><br></pre></td></tr></table></figure>

<p>update column property:<br> <figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; alter table &#123;TABLE_NAME&#125; modify name varchar(22);</span><br></pre></td></tr></table></figure></p>
<p>show table size:</p>
<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select concat(round(sum(DATA_LENGTH/1024/1024),2),&apos;MB&apos;) as data from information_schema.TABLES WHERE TABLE_SCHEMA=&apos;&#123;DATABASE_NAME&#125;&apos; and TABLE_NAME = &apos;&#123;TABLE_NAME&#125;&apos;;</span><br></pre></td></tr></table></figure>

<p><span id="db-character">update database default character:</span></p>
<figure class="highlight plain hljs"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; ALTER DATABASE &#123;DATABASE_NAME&#125; CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;</span><br></pre></td></tr></table></figure>
        </div>
        
        <div class="level is-size-7 is-uppercase">
            <div class="level-start">
                <div class="level-item">
                    <span class="is-size-6 has-text-grey has-mr-7">#</span>
                    <a class="has-link-grey -link" href="/tags/Mysql/">Mysql</a>, <a class="has-link-grey -link" href="/tags/SlowLog/">SlowLog</a>, <a class="has-link-grey -link" href="/tags/Ubuntu/">Ubuntu</a>
                </div>
            </div>
        </div>
        
        
        
            <p class="is-size-7">
    <b>
    Thank you for reading.<br>
    This post is copyrighted by <a href="https://liyuliang.cc">Liyuliang’s Blog.</a><br>
    If reproduced, please indicate the source: <a href="https://liyuliang.cc">Liyuliang’s Blog</a><br>
    This blog uses <a target="_blank" rel="license" href="http://creativecommons.org/licenses/by-nc-sa/4.0/">Creative Commons Attribution-NonCommercial-Share-Sharing 4.0 International License Agreement </a> to license.<br>
    </b>
    <hr>
</p>
        
        
    </div>
</div>



<div class="card">
    <div class="card-content">
        <h3 class="menu-label has-text-centered">Like this article? Support the author with</h3>
        <div class="buttons is-centered">
            
                
<a class="button is-info donate">
    <span class="icon is-small">
        <i class="fab fa-alipay"></i>
    </span>
    <span>Alipay</span>
    <div class="qrcode"><img src="/images/alipay.jpeg" alt="Alipay"></div>
</a>

                
                
<a class="button is-success donate">
    <span class="icon is-small">
        <i class="fab fa-weixin"></i>
    </span>
    <span>Wechat</span>
    <div class="qrcode"><img src="/images/wxpay.jpeg" alt="Wechat"></div>
</a>

                
                <!-- Visit https://www.paypal.com/donate/buttons/ to get your donate button -->

<a class="button is-warning donate" onclick="document.getElementById(&#39;paypal-donate-form&#39;).submit()">
    <span class="icon is-small">
        <i class="fab fa-paypal"></i>
    </span>
    <span>Paypal</span>
</a>
<form action="https://www.paypal.com/cgi-bin/webscr" method="post" target="_blank" id="paypal-donate-form">
    <input type="hidden" name="cmd" value="_donations">
    <input type="hidden" name="business" value="D7RUA6PUE8GM2">
    <input type="hidden" name="currency_code" value="USD">
</form>

                
        </div>
    </div>
</div>



<div class="card card-transparent">
    <div class="level post-navigation is-flex-wrap is-mobile">
        
        <div class="level-start">
            <a class="level level-item has-link-grey  article-nav-prev" href="/2017/08/30/Build-new-mysql-slave-without-stop-service-or-locked-table-by-Xtrabackup/">
                <i class="level-item fas fa-chevron-left"></i>
                <span class="level-item">Build new mysql slave without stop service or locked table by Xtrabackup</span>
            </a>
        </div>
        
        
        <div class="level-end">
            <a class="level level-item has-link-grey  article-nav-next" href="/2017/08/26/Mysql-Table-Horizontal-Split-By-Mycat/">
                <span class="level-item">Mysql Table Horizontal Split By Mycat</span>
                <i class="level-item fas fa-chevron-right"></i>
            </a>
        </div>
        
    </div>
</div>


</div>
                
                




<div class="column is-4-tablet is-4-desktop is-4-widescreen  has-order-3 column-right is-sticky">
    
        <div class="card widget">
    <div class="card-content">
        <div class="menu">
        <h3 class="menu-label">
            Archives
        </h3>
        <ul class="menu-list">
        
        <li>
            <a class="level is-marginless" href="/archives/2018/06/">
                <span class="level-start">
                    <span class="level-item">June 2018</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">3</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/archives/2018/05/">
                <span class="level-start">
                    <span class="level-item">May 2018</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">1</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/archives/2018/04/">
                <span class="level-start">
                    <span class="level-item">April 2018</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">1</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/archives/2018/03/">
                <span class="level-start">
                    <span class="level-item">March 2018</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">3</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/archives/2018/02/">
                <span class="level-start">
                    <span class="level-item">February 2018</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">3</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/archives/2018/01/">
                <span class="level-start">
                    <span class="level-item">January 2018</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">6</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/archives/2017/12/">
                <span class="level-start">
                    <span class="level-item">December 2017</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">3</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/archives/2017/11/">
                <span class="level-start">
                    <span class="level-item">November 2017</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">2</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/archives/2017/10/">
                <span class="level-start">
                    <span class="level-item">October 2017</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">5</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/archives/2017/09/">
                <span class="level-start">
                    <span class="level-item">September 2017</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">8</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/archives/2017/08/">
                <span class="level-start">
                    <span class="level-item">August 2017</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">10</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/archives/2017/07/">
                <span class="level-start">
                    <span class="level-item">July 2017</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">3</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/archives/2017/06/">
                <span class="level-start">
                    <span class="level-item">June 2017</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">6</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/archives/2017/05/">
                <span class="level-start">
                    <span class="level-item">May 2017</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">9</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/archives/2017/04/">
                <span class="level-start">
                    <span class="level-item">April 2017</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">7</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/archives/2017/03/">
                <span class="level-start">
                    <span class="level-item">March 2017</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">9</span>
                </span>
            </a>
        </li>
        
        </ul>
        </div>
    </div>
</div>
    
        
<div class="card widget">
    <div class="card-content">
        <h3 class="menu-label">
            Tag Cloud
        </h3>
        <a href="/tags/Alipay/" style="font-size: 10px;">Alipay</a> <a href="/tags/Apache/" style="font-size: 12px;">Apache</a> <a href="/tags/Baidu/" style="font-size: 10px;">Baidu</a> <a href="/tags/Canal/" style="font-size: 10px;">Canal</a> <a href="/tags/Centos/" style="font-size: 10px;">Centos</a> <a href="/tags/CloudDisk/" style="font-size: 10px;">CloudDisk</a> <a href="/tags/Daemon/" style="font-size: 11px;">Daemon</a> <a href="/tags/Elasticsearch/" style="font-size: 13px;">Elasticsearch</a> <a href="/tags/Filebeat/" style="font-size: 11px;">Filebeat</a> <a href="/tags/Glide/" style="font-size: 10px;">Glide</a> <a href="/tags/Iterm/" style="font-size: 10px;">Iterm</a> <a href="/tags/JQuery/" style="font-size: 10px;">JQuery</a> <a href="/tags/Keepalived/" style="font-size: 10px;">Keepalived</a> <a href="/tags/Kibana/" style="font-size: 11px;">Kibana</a> <a href="/tags/Linux/" style="font-size: 10px;">Linux</a> <a href="/tags/Login/" style="font-size: 10px;">Login</a> <a href="/tags/Logstash/" style="font-size: 11px;">Logstash</a> <a href="/tags/Mac/" style="font-size: 14px;">Mac</a> <a href="/tags/Mongodb/" style="font-size: 11px;">Mongodb</a> <a href="/tags/Mycat/" style="font-size: 10px;">Mycat</a> <a href="/tags/Mysql/" style="font-size: 19px;">Mysql</a> <a href="/tags/NFS/" style="font-size: 10px;">NFS</a> <a href="/tags/Nginx/" style="font-size: 10px;">Nginx</a> <a href="/tags/Nodejs/" style="font-size: 10px;">Nodejs</a> <a href="/tags/PHP/" style="font-size: 18px;">PHP</a> <a href="/tags/Pagespeed/" style="font-size: 10px;">Pagespeed</a> <a href="/tags/Partition/" style="font-size: 10px;">Partition</a> <a href="/tags/Redis/" style="font-size: 12px;">Redis</a> <a href="/tags/Replica/" style="font-size: 10px;">Replica</a> <a href="/tags/Security/" style="font-size: 10px;">Security</a> <a href="/tags/Slave/" style="font-size: 13px;">Slave</a> <a href="/tags/SlowLog/" style="font-size: 11px;">SlowLog</a> <a href="/tags/Symfony/" style="font-size: 15px;">Symfony</a> <a href="/tags/TCP/" style="font-size: 10px;">TCP</a> <a href="/tags/Twig/" style="font-size: 11px;">Twig</a> <a href="/tags/Ubuntu/" style="font-size: 20px;">Ubuntu</a> <a href="/tags/Varnish/" style="font-size: 10px;">Varnish</a> <a href="/tags/Vim/" style="font-size: 10px;">Vim</a> <a href="/tags/Virtualbox/" style="font-size: 10px;">Virtualbox</a> <a href="/tags/Wordpress/" style="font-size: 11px;">Wordpress</a> <a href="/tags/auth/" style="font-size: 11px;">auth</a> <a href="/tags/backup/" style="font-size: 10px;">backup</a> <a href="/tags/beanstalkd/" style="font-size: 10px;">beanstalkd</a> <a href="/tags/binlog/" style="font-size: 10px;">binlog</a> <a href="/tags/blog/" style="font-size: 10px;">blog</a> <a href="/tags/brew/" style="font-size: 11px;">brew</a> <a href="/tags/composer/" style="font-size: 10px;">composer</a> <a href="/tags/cookie/" style="font-size: 10px;">cookie</a> <a href="/tags/css/" style="font-size: 10px;">css</a> <a href="/tags/dante/" style="font-size: 10px;">dante</a> <a href="/tags/docker/" style="font-size: 12px;">docker</a> <a href="/tags/domain/" style="font-size: 11px;">domain</a> <a href="/tags/download/" style="font-size: 10px;">download</a> <a href="/tags/file/" style="font-size: 10px;">file</a> <a href="/tags/fork/" style="font-size: 10px;">fork</a> <a href="/tags/git/" style="font-size: 11px;">git</a> <a href="/tags/github/" style="font-size: 10px;">github</a> <a href="/tags/gitlab/" style="font-size: 10px;">gitlab</a> <a href="/tags/golang/" style="font-size: 17px;">golang</a> <a href="/tags/homestead/" style="font-size: 10px;">homestead</a> <a href="/tags/iptable/" style="font-size: 11px;">iptable</a> <a href="/tags/laravel/" style="font-size: 10px;">laravel</a> <a href="/tags/log/" style="font-size: 10px;">log</a> <a href="/tags/nginx/" style="font-size: 16px;">nginx</a> <a href="/tags/proxy/" style="font-size: 11px;">proxy</a> <a href="/tags/redis-cluster/" style="font-size: 10px;">redis-cluster</a> <a href="/tags/redis-sentinel/" style="font-size: 10px;">redis-sentinel</a> <a href="/tags/sock5/" style="font-size: 10px;">sock5</a> <a href="/tags/supervisor/" style="font-size: 10px;">supervisor</a> <a href="/tags/sync/" style="font-size: 10px;">sync</a> <a href="/tags/tips/" style="font-size: 11px;">tips</a> <a href="/tags/tmux/" style="font-size: 10px;">tmux</a> <a href="/tags/usb/" style="font-size: 10px;">usb</a> <a href="/tags/vagrant/" style="font-size: 10px;">vagrant</a> <a href="/tags/web/" style="font-size: 10px;">web</a>
    </div>
</div>

    
        


    
    
</div>

            </div>
        </div>
    </section>
    <footer class="footer">
    <div class="container">
        <div class="level">
            <div class="level-start has-text-centered-mobile">
                <a class="footer-logo is-block has-mb-6" href="/">
                
                    Liyuliang&#39;s Blog
                
                </a>

                <p class="is-size-7">
                    <a href="/copyright" target="_blank">Copyright</a>
                    &copy; 2022 Liyuliang&nbsp;

		    <a target="_blank" href="https://beian.miit.gov.cn">粤ICP备18120141号</a>
                </p>
            </div>
            <div class="level-end">
            
                <div class="field has-addons is-flex-center-mobile has-mt-5-mobile is-flex-wrap is-flex-middle">
                
                
                <p class="control">
                    <a class="button is-white is-large" target="_blank" title="Creative Commons" href="https://creativecommons.org/">
                        
                        <i class="fab fa-creative-commons"></i>
                        
                    </a>
                </p>
                
                <p class="control">
                    <a class="button is-white is-large" target="_blank" title="Attribution 4.0 International" href="http://creativecommons.org/licenses/by-nc-sa/4.0/">
                        
                        <i class="fab fa-creative-commons-by"></i>
                        
                    </a>
                </p>
                
                <p class="control">
                    <a class="button is-white is-large" target="_blank" title="Download on GitHub" href="https://github.com/liyuliang">
                        
                        <i class="fab fa-github"></i>
                        
                    </a>
                </p>
                
                <p class="control">
                    <a class="button is-white is-large" target="_blank" title="SiteMap for Google" href="https://liyuliang.cc/sitemap.xml">
                        
                        <i class="fab fa-google"></i>
                        
                    </a>
                </p>
                
                <p class="control">
                    <a class="button is-white is-large" target="_blank" title="SiteMap for Baidu" href="https://liyuliang.cc/baidusitemap.xml">
                        
                        <i class="fab fa-bimobject"></i>
                        
                    </a>
                </p>
                
                </div>
            
            </div>
        </div>
    </div>
</footer>

    <script src="https://cdn.jsdelivr.net/npm/jquery@3.3.1/dist/jquery.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/moment@2.22.2/min/moment-with-locales.min.js"></script>
<script>moment.locale("en");</script>


    
    
    
    <script src="/js/animation.js"></script>
    

    
    
    
    <script src="https://cdn.jsdelivr.net/npm/lightgallery@1.6.8/dist/js/lightgallery.min.js" defer></script>
    <script src="https://cdn.jsdelivr.net/npm/justifiedGallery@3.7.0/dist/js/jquery.justifiedGallery.min.js" defer></script>
    <script src="/js/gallery.js" defer></script>
    

    
    

<div id="outdated">
    <h6>Your browser is out-of-date!</h6>
    <p>Update your browser to view this website correctly. <a id="btnUpdateBrowser" href="http://outdatedbrowser.com/">Update
            my browser now </a></p>
    <p class="last"><a href="#" id="btnCloseUpdateBrowser" title="Close">&times;</a></p>
</div>
<script src="https://cdn.jsdelivr.net/npm/outdatedbrowser@1.1.5/outdatedbrowser/outdatedbrowser.min.js" defer></script>
<script>
    document.addEventListener("DOMContentLoaded", function () {
        outdatedBrowser({
            bgColor: '#f25648',
            color: '#ffffff',
            lowerThan: 'flex'
        });
    });
</script>


    
    
<script src="https://cdn.jsdelivr.net/npm/mathjax@2.7.5/unpacked/MathJax.js?config=TeX-MML-AM_CHTML" defer></script>
<script>
document.addEventListener('DOMContentLoaded', function () {
    MathJax.Hub.Config({
        'HTML-CSS': {matchFontHeight: false},
        SVG: {matchFontHeight: false},
        CommonHTML: {matchFontHeight: false}
    });
});
</script>

    
    

<a id="back-to-top" title="Back to Top" href="javascript:;">
    <i class="fas fa-chevron-up"></i>
</a>
<script src="/js/back-to-top.js" defer></script>


    
    

    
    
    
    

    
    
    
    
    
    <script src="https://cdn.jsdelivr.net/npm/clipboard@2.0.4/dist/clipboard.min.js" defer></script>
    <script src="/js/clipboard.js" defer></script>
    

    


<script src="/js/main.js" defer></script>

    
    <div class="searchbox ins-search">
    <div class="searchbox-container ins-search-container">
        <div class="searchbox-input-wrapper">
            <input type="text" class="searchbox-input ins-search-input" placeholder="Type something...">
            <span class="searchbox-close ins-close ins-selectable"><i class="fa fa-times-circle"></i></span>
        </div>
        <div class="searchbox-result-wrapper ins-section-wrapper">
            <div class="ins-section-container"></div>
        </div>
    </div>
</div>
<script>
    (function (window) {
        var INSIGHT_CONFIG = {
            TRANSLATION: {
                POSTS: 'Posts',
                PAGES: 'Pages',
                CATEGORIES: 'Categories',
                TAGS: 'Tags',
                UNTITLED: '(Untitled)',
            },
            CONTENT_URL: '/content.json',
        };
        window.INSIGHT_CONFIG = INSIGHT_CONFIG;
    })(window);
</script>
<script src="/js/insight.js" defer></script>
<link rel="stylesheet" href="/css/search.css">
<link rel="stylesheet" href="/css/insight.css">
    
</body>
</html>